package cn.edu.scau.cmi.chenbingquan.customize.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import cn.edu.scau.cmi.chenbingquan.customize.domain.Teacher;

// 对数据库中教师表的操作(老师表的属性有：id(主键)，name)
public class TeacherDAOByJdbc {

	// 添加教师
	public void newTeacher(Teacher teacher) {
		Connection conn = JdbcConnectionUtil.getConnection();
		String sql = "insert into teacher(id, name) values(?, ?)";
		
		PreparedStatement ps;
		try {
			ps = (PreparedStatement)conn.prepareStatement(sql);
			ps.setLong(1, teacher.getId());			// 属性下标加取值
			ps.setString(2, teacher.getName());
			ps.executeUpdate();
			
			ps.close();			// 关闭连接
			conn.close();
			System.out.println("添加成功");
		} catch(SQLException e) {
			System.out.println("添加出现错误");
		}
	}

	// 删除教师
	public void delete(Teacher teacher) {
		Connection conn = JdbcConnectionUtil.getConnection();
		String sql = "delete from teacher where id = " + teacher.getId();
		
		PreparedStatement ps;
		try {
			ps = (PreparedStatement)conn.prepareStatement(sql);
			ps.executeUpdate();
			ps.close();
			conn.close();
			System.out.println("删除成功");
		} catch(SQLException e) {
			System.out.println("删除出现错误");
		}
	}
	
	// 修改教师
	public void update(Teacher teacher) {
		Connection conn = JdbcConnectionUtil.getConnection();
		String sql = "update teacher set name = '" + teacher.getName() + "'" + "where id =" + teacher.getId();
		
		PreparedStatement ps;
		try {
			ps = (PreparedStatement)conn.prepareStatement(sql);
			ps.executeUpdate();
			ps.close();
			conn.close();
			System.out.println("修改成功");
		} catch(SQLException e) {
			System.out.println("修改出现错误");
		}
	}

	// 选择学生(学生无老师时才可以选择)
	public void selectStudent(Teacher teacher, long student_id) {
		Connection conn = JdbcConnectionUtil.getConnection();
		String sql1 = "select teacher_id from student where student_id =" + student_id;
		String sql2 = "update student set teacher_id = " + teacher.getId() 
												+ " where student_id =" + student_id;		// 注意空格问题
		
		PreparedStatement ps;
		try {
			ps = (PreparedStatement)conn.prepareStatement(sql1);
			ResultSet rs = ps.executeQuery();
			rs.next();
			
			if(rs.getString(1) == null) {
				ps = (PreparedStatement)conn.prepareStatement(sql2);
				ps.executeUpdate();
				ps.close();
				conn.close();
				System.out.println("选择学生成功");
			} else {
				System.out.println("该学生已有老师");
				ps.close();
				conn.close();
			}
		} catch(SQLException e) {
			System.out.println("选择学生出现错误");
		}
	}
		
	// 显示所有老师信息
	public void listAllTeachers() {
		Connection conn = JdbcConnectionUtil.getConnection();
		String sql = "select* from teacher";
		
		PreparedStatement ps;
		try {
			ps = (PreparedStatement)conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			
			int col = rs.getMetaData().getColumnCount();		// 列数(即属性个数)
			System.out.println("teacher:");
			System.out.println("============================");
			System.out.println(String.format("%-13s%-13s", "id", "name"));
			while(rs.next()) {
				for(int i=1; i<=col; i++) {
					System.out.print(String.format("%-13s", rs.getString(i)));		// 输出属性值
				}
				System.out.println();
			}
			System.out.println("============================");
		} catch(SQLException e) {
			e.printStackTrace();
		}
	}
}